#!/bin/bash
# script name: 自动授权脚本（指定数据库表只读授权操作）
# author: subushi
# update date: 2025.10.27

#==========================================================#
#    Docker 版 MySQL 自动授权脚本：robot 表只读权限        #
#  功能：为 report_user 授予 3 个库中所有                  #
#        dcs_new_robot_operation_data_* 表的 SELECT 权限   #
#  环境：MySQL 运行在名为 'mysql8_new' 的 Docker 容器中    #
#==========================================================#

# ------------------ 配置区域 ------------------
CONTAINER_NAME="mysql8_new"
MYSQL_USER="root"
MYSQL_PASS="RS***"  # 容器内 root 密码

USER="report_user"
HOST_PATTERN="%"

DBS="gacmotor_lsb_data_db gacmotor_rsb_data_db gacmotor_ub_data_db"
TABLE_PATTERN="dcs_new_robot_operation_data_%"
LOG_FILE="/opt/mysql_report/mysql_grant_robot.log"

# ------------------ 工具函数 ------------------
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

error_exit() {
    echo "❌ [$SECONDS] ERROR: $1" >&2 | tee -a "$LOG_FILE"
    exit 1
}

# ------------------ 安全检查 ------------------
if ! docker inspect "$CONTAINER_NAME" >/dev/null 2>&1; then
    error_exit "Docker 容器 '$CONTAINER_NAME' 不存在，请检查容器名称"
fi

# 检查容器是否运行
if [ "$(docker inspect -f '{{.State.Running}}' "$CONTAINER_NAME")" != "true" ]; then
    error_exit "Docker 容器 '$CONTAINER_NAME' 未运行，请先启动"
fi

log "✅ 开始为用户 '$USER'@$HOST_PATTERN 授予 robot 表只读权限（通过容器 $CONTAINER_NAME）"

# ------------------ 主逻辑 ------------------
TOTAL_GRANTED=0

for DB in $DBS; do
    log "🔍 检查数据库: $DB"

    # 在容器内执行 SQL 查询，生成 GRANT 语句
    GRANT_SQL=$(docker exec -i "$CONTAINER_NAME" mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -N -s << EOF
SELECT CONCAT('GRANT SELECT ON ', TABLE_SCHEMA, '.', TABLE_NAME, ' TO ''$USER''@''$HOST_PATTERN'';')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '$DB'
  AND TABLE_NAME LIKE '$TABLE_PATTERN'
  AND NOT EXISTS (
    SELECT 1 FROM information_schema.TABLE_PRIVILEGES 
    WHERE GRANTEE = CONCAT('''$USER''@''$HOST_PATTERN''')
      AND TABLE_SCHEMA = TABLES.TABLE_SCHEMA
      AND TABLE_NAME = TABLES.TABLE_NAME
      AND PRIVILEGE_TYPE = 'SELECT'
  );
EOF
)

    # 检查是否查询出错
    if [ $? -ne 0 ]; then
        error_exit "在容器中执行 MySQL 查询失败，请检查账号密码或权限"
    fi

    # 执行授权
    if [ -n "$GRANT_SQL" ]; then
        echo "$GRANT_SQL" | docker exec -i "$CONTAINER_NAME" mysql -u"$MYSQL_USER" -p"$MYSQL_PASS"
        EXIT_CODE=$?
        if [ $EXIT_CODE -ne 0 ]; then
            error_exit "执行 GRANT 语句失败"
        fi
        COUNT=$(echo "$GRANT_SQL" | grep -c ";")
        log "✅ 已为 $DB 授予 $COUNT 个新表权限"
        ((TOTAL_GRANTED += COUNT))
    else
        log "✅ $DB: 无新表需要授权"
    fi
done

# ------------------ 结果汇总 ------------------
if [ $TOTAL_GRANTED -gt 0 ]; then
    log "🎉 成功为 '$USER'@'$HOST_PATTERN' 授予 $TOTAL_GRANTED 个新表的 SELECT 权限"
else
    log "🟢 所有表权限已同步，无需更新"
fi